﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Xqsys.COM
{
    public class SqlHelper
    {
        private static SqlConnection createcon()
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["dbcon"].ToString();
            return con;
        }
        /// <summary>
        /// 增删改数据
        /// </summary>
        /// <param name="storename">存储过程名</param>
        /// <param name="parms">存储过程参数</param>
        /// <returns>bool 成功与否</returns>
        public static bool ExecuteNonQuery(string storename,SqlParameter[] param)
        {
            SqlConnection con = createcon();
            SqlCommand com = new SqlCommand();
            try
            {
                con.Open();
                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = storename;
                com.Connection = con;
                for (int i = 0; i < param.Length; i++)
                {
                    com.Parameters.Add(param[i]);
                }
                if (com.ExecuteNonQuery() <= 0)
                    return false;
            }
            catch (SqlException ex)
            {
                return false;
            }
            finally
            {
                com.Dispose();
                con.Close();
                con.Dispose();
            }
                return true;
        }
        /// <summary>
        /// 增删改数据
        /// </summary>
        /// <param name="storename">存储过程名</param>
        /// <param name="parms">存储过程参数</param>
        /// <returns>bool 成功与否</returns>
        public static bool ExecuteNonQuery(string storename)
        {
            SqlConnection con = createcon();
            SqlCommand com = new SqlCommand();
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = storename;
            try
            {
                con.Open();
                com.Connection = con;
                if (com.ExecuteNonQuery() > 0)
                {
                    return true;
                }
                else
                    return false;
            }
            catch (SqlException ex)
            {
                return false;
            }
            finally
            {
                con.Dispose();
                con.Close();
                com.Dispose();
            }
        }
        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="storagename">存储过程名字</param>
        /// <param name="parms">参数</param>
        /// <returns>返回DataSet</returns>
        public static DataSet ExecuteQuery(string storagename,SqlParameter[] param)
        {
            SqlConnection con = createcon();
            SqlCommand com=new SqlCommand();
            com.CommandType=CommandType.StoredProcedure;
            com.CommandText=storagename;
            SqlDataAdapter da=new SqlDataAdapter();
            DataSet ds=new DataSet();
            try
            {
                con.Open();
                com.Connection=con;
                for(int i=0;i<param.GetLength(0);i++)
                {
                    com.Parameters.Add(param[i]);
                }
                da.SelectCommand=com;
                da.Fill(ds);
            }
            catch(SqlException ex)
            {
                return null;
            }
            finally 
            {
                con.Dispose();
                con.Close();
                com.Dispose();
                da.Dispose();
            }
            return ds;
        }
        /// <summary>
        /// 查寻数据
        /// </summary>
        /// <param name="storagename">存储过程名字</param>
        /// <returns>返回Dataset</returns> 
        public static DataSet ExecuteQuery(string storagename)
        {
            SqlConnection con = createcon();
            SqlCommand com = new SqlCommand();
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = storagename;
            com.Connection = con;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = com;
            DataSet ds = new DataSet();
            try
            {
                con.Open();             
                da.Fill(ds);
            }
            catch (SqlException ex)
            {
                return null;
            }
            finally
            {
                con.Dispose();
                con.Close();
                com.Dispose();
                da.Dispose();
            }
            return ds;
        }
        /// <summary>
        /// 查询单行数据
        /// </summary>
        /// <param name="storagename">存储过程名字</param>
        /// <param name="param">参数</param>
        /// <returns>返回object</returns>
        public static object ExecuteScalar(string storagename, SqlParameter[] param)
        {
            SqlConnection con = createcon();
            SqlCommand com = new SqlCommand();
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = storagename;
            com.Connection = con;
            try
            {
                con.Open();
                
                for (int i=0; i < param.GetLength(0); i++)
                {
                    com.Parameters.Add(param[i]);
                }
                return com.ExecuteScalar();
            }
            catch (SqlException ex)
            {
                return null;
            }
            finally
            {
                con.Dispose();
                con.Close();
                com.Dispose();
            }
        }
        public static DataSet ExecuteQuery(string storagename, SqlParameter[] parms, int currentPage, int pagesize, out int num, string returnstring)
        {
            SqlConnection con = createcon();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storagename;
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();

            try
            {
                con.Open();
                cmd.Connection = con;
                int startRow = (currentPage - 1) * pagesize;
                for (int i = 0; i < parms.GetLength(0); i++)
                {
                    cmd.Parameters.Add(parms[i]);
                }
                da.SelectCommand = cmd;
                da.Fill(ds, startRow, pagesize, "ds");
                num = int.Parse(cmd.Parameters[returnstring].Value.ToString());
                return ds;
            }
            catch
            {
                num = 0;
                return null;
            }
            finally
            {
                con.Dispose();
                con.Close();
                cmd.Dispose();
                da.Dispose();
            }

        }


    }
}
 